package com.hui.dao.impl;

import com.hui.dao.CourseContentDao;
import com.hui.pojo.Course;
import com.hui.pojo.Course_Lesson;
import com.hui.pojo.Course_Section;
import com.hui.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 *
 * @Auther: 李昭辉
 * @Date: 2021/07/13/10:58
 * @Description:
 */
public class CourseContentDaoImpl implements CourseContentDao {

    // 插入课时
    @Override
    public int insertLesson(Course_Lesson lesson) {
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            String sql = "INSERT INTO course_lesson (\n" +
                    "course_id,\n" +
                    "section_id,\n" +
                    "theme,\n" +
                    "duration,\n" +
                    "is_free,\n" +
                    "order_num,\n" +
                    "create_time,\n" +
                    "update_time,\n" +
                    "STATUS\n" +
                    ")VALUES (?,?,?,?,?,?,?,?,?)";
            Object[] params = {
                    lesson.getCourse_id(), lesson.getSection_id(), lesson.getTheme(),
                    lesson.getDuration(), lesson.getIs_free(), lesson.getOrder_num(),
                    lesson.getCreate_time(), lesson.getUpdate_time(), lesson.getStatus()};
            // 执行 SQL 语句，并把结果返回出去
            return qr.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    // 修改课时
    @Override
    public int updateLesson(Course_Lesson lesson) {
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            String sql = "UPDATE course_lesson SET " +
                    "section_id=?," +
                    "theme=?," +
                    "duration=?," +
                    "is_free=?," +
                    "order_num=?," +
                    "update_time=? " +
                    "WHERE id=?";
            Object[] params = {
                    lesson.getSection_id(), lesson.getTheme(), lesson.getDuration(),
                    lesson.getIs_free(), lesson.getOrder_num(), lesson.getUpdate_time(),
                    lesson.getId()};
            // 执行 SQL 语句，并把结果返回
            return qr.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    // 根据课程id获取所有章节
    @Override
    public List<Course_Section> getSectionByCourseId(int courseId) {
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            String sql = "SELECT \n" +
                    "id, \n" +
                    "course_id,\n" +
                    "section_name,\n" +
                    "description,\n" +
                    "order_num,\n" +
                    "`status`,\n" +
                    "create_time,\n" +
                    "update_time,\n" +
                    "is_del\n" +
                    "FROM course_section WHERE course_id = ? ";
            // 执行sql语句获取结果
            List<Course_Section> sectionList = qr.query(sql, new BeanListHandler<>(Course_Section.class), courseId);
            // 遍历所有的章节信息，根据id获取每一个章节的所有课时信息
            for (Course_Section section : sectionList) {
                List<Course_Lesson> lessonList = this.getLessonBuSectionId(section.getId());
                section.setLessonList(lessonList);
            }
            return sectionList;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    // 根据章节id获取所有课时信息
    @Override
    public List<Course_Lesson> getLessonBuSectionId(int sectionId) {
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            String sql = "SELECT \n" +
                    "id,\n" +
                    "course_id,\n" +
                    "section_id,\n" +
                    "theme,\n" +
                    "duration,\n" +
                    "is_free,\n" +
                    "order_num,\n" +
                    "STATUS,\n" +
                    "create_time,\n" +
                    "update_time,\n" +
                    "is_del\n" +
                    "FROM course_lesson WHERE section_id = ?";
            // 执行sql语句获取结果，并返回
            List<Course_Lesson> lessonList = qr.query(sql, new BeanListHandler<>(Course_Lesson.class), sectionId);
            return lessonList;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    // 根据id获取课程回显信息
    @Override
    public Course getCourseById(int id) {
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            String sql = "SELECT id,course_name FROM course WHERE id = ? ";
            Course course = qr.query(sql, new BeanHandler<>(Course.class), id);
            return course;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    // 插入章节信息
    @Override
    public int insertSection(Course_Section section) {
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            String sql = "INSERT INTO course_section (\n" +
                    "course_id,\n" +
                    "section_name,\n" +
                    "description,\n" +
                    "order_num,\n" +
                    "create_time,\n" +
                    "update_time,\n" +
                    "STATUS\n" +
//                    "is_del\n" +
                    ") VALUES(?,?,?,?,?,?,?)";
            Object[] params = {
                    section.getCourse_id(), section.getSection_name(), section.getDescription(),
                    section.getOrder_num(), section.getCreate_time(), section.getUpdate_time(),
                    section.getStatus()};
            // 执行sql语句，并将结果返回
            return qr.update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    // 修改章节信息
    @Override
    public int updateSection(Course_Section section) {
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            String sql = "UPDATE course_section SET \n" +
                    "section_name = ?,\n" +
                    "description = ?,\n" +
                    "order_num = ?,\n" +
                    "update_time = ?\n" +
                    "WHERE id = ?";
            Object[] params = {
                    section.getSection_name(), section.getDescription(), section.getOrder_num(),
                    section.getUpdate_time(), section.getId()
            };
            // 执行sql语句,并把结果返回
            return qr.update(sql, params);

        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    // 修改章节状态
    @Override
    public int updateStatusById(int id, int status) {
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            String sql = "update course_section set status = ? where id = ?";
            // 执行修改 SQL 语句 ， 并将结果返回
            return qr.update(sql, status, id);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }
}
